package com.nhim.pool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import com.nhim.utility.COMUtil;

/**
 * 오라클용 쿼리 도와주기
 * @author wonsama
 *
 */
public class WQueryHelper {
	
	/////////////////////////////////////////////////////////////////////////
	//
	//	MESSAGE
	//
	
	private static final String LOG_SELECT_PREFIX = "[selectDefault] query :\n";
	private static final String LOG_SELECT_ERROR = "[selectDefault] Exception : ";
	private static final String LOG_SELECT_COUNT = "[selectDefault] count : ";
	private static final String LOG_SELECT_COUNT_TITLE = "CNT \t";
	private static final String LOG_SELECT_RESULT = "[selectDefault] result : \n";
	
	
	private static final String LOG_UPDATE_PREFIX = "[updateDefault] query :\n";
	private static final String LOG_UPDATE_ERROR = "[updateDefault] Exception : ";

	/////////////////////////////////////////////////////////////////////////
	//
	//	QUERY
	//

	/**
	 * 기본 조회 쿼리
	 * @param query 쿼리 입력 받을 파라미터 구분은 ? 로 한다
	 * @param setParams  파라미터 목록 : 순차적으로 STRING 형태로 넣어준다
	 * @param getResult 결과 목록 : 순차적으로 STRING 형태로 넣어준다
	 * @return 처리 결과 목록 
	 * @throws Exception 오류
	 */
	public static List<HashMap<String, String>> selectDefault(String query, List<String> setParams,  List<String> getResult) throws Exception
	{
		ArrayList<HashMap<String, String>> alist = new ArrayList<HashMap<String, String>>();

		Connection oConn = null;
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		try{
			oConn = ConnectionPool.getConnection();

			pstmt = oConn.prepareStatement( query );
			pstmt.clearParameters();

			int i=1;
			if(setParams!=null){
				for(String param:setParams){
					pstmt.setString(i++, param);	
				}	
			}
			rs = pstmt.executeQuery();

			while (rs.next()){
				HashMap<String, String> reMap = new HashMap<String, String>(getResult.size());
				if(getResult!=null){
					for(String result:getResult){
						reMap.put( result , rs.getString( result ) );	
					}	
				}
				alist.add(reMap);
			}	
		}
		catch(Exception e1) {
			COMUtil.debug( LOG_SELECT_ERROR  + e1.toString());
			throw new Exception( LOG_SELECT_ERROR + e1.toString());
		}
		finally {
			try{
				if(rs != null) { rs.close(); rs=null;}
				if(pstmt != null){ pstmt.close(); pstmt=null;}
				if(oConn != null){ ConnectionPool.close(oConn); oConn=null;}	
			}catch(Exception e2){
				COMUtil.debug( LOG_SELECT_ERROR + e2.toString());		
			}
		}

		return alist;
	}

	/**
	 * 변경/삽입/삭제 용 쿼리
	 * @param query 쿼리
	 * @param setParams 파라미터 목록 : 순차적으로 STRING 형태로 넣어준다
	 * @return 처리 결과 수치
	 * @throws Exception 오류
	 */
	public static int updateDefault(String query, List<String> setParams) throws Exception{

		int returnVal=0;

		Connection oConn = null;
		PreparedStatement pstmt = null;		
		ResultSet rs = null;
		
		
		COMUtil.debug( LOG_UPDATE_PREFIX + getQuery( query , setParams ) );

		try{
			oConn =  ConnectionPool.getConnection();


			pstmt = oConn.prepareStatement( query );
			pstmt.clearParameters();

			int i=1;
			if(setParams!=null){
				for(String param:setParams){
					pstmt.setString(i++, param);	
				}	
			}
			returnVal = pstmt.executeUpdate();
		}
		catch(Exception e1){
			COMUtil.debug( LOG_UPDATE_ERROR + e1.toString() );
			throw new Exception( LOG_UPDATE_ERROR + e1.toString() );
		}
		finally{
			try{
				if(rs != null){ rs.close(); rs=null;} 
				if(pstmt != null){ pstmt.close(); pstmt=null;} 
				if(oConn != null){ ConnectionPool.close(oConn); oConn=null;}	
			}catch (Exception e2) {
				COMUtil.debug( LOG_UPDATE_ERROR + e2.toString() );
			}
		}

		return returnVal;
	}

	/**
	 * 커넥션 풀을 종료한다
	 */
	public static void close() throws Exception
	{
		 ConnectionPool.getConnection().close();
	}

	/**
	 * 입력받은 쿼리를 얻어온다
	 * @param query 실제 쿼리 
	 * @param setParams 파라미터
	 * @return 쿼리
	 */
	private static String getQuery(String query, List<String> setParams)
	{
		if(setParams==null)
			return query;

		for(String param:setParams){
			query = query.replaceFirst("\\?", param);
		}

		return query; 
	}

	/**
	 * SELECT 결과를 LOG 상으로 출력한다
	 * @param query 쿼리
	 * @param setParams 파라미터
	 * @param getResult 결과필드 
	 * @throws Exception 오류
	 */
	public static void printQueryResult(String query, List<String> setParams,  List<String> getResult) throws Exception
	{
		List<HashMap<String, String>> result = selectDefault(query, setParams, getResult);

		COMUtil.debug( LOG_SELECT_PREFIX + getQuery( query , setParams ) );
		
		//PRINT : COUNT
		COMUtil.debug( LOG_SELECT_COUNT + result.size() );

		//PRINT : INIT 
		StringBuffer sb = new StringBuffer();
		sb.append( LOG_SELECT_RESULT );

		//PRINT : TITLE
		sb.append( LOG_SELECT_COUNT_TITLE );
		if(getResult!=null){
			Iterator<String> itTitle = getResult.iterator();
			while(itTitle.hasNext()){
				sb.append( itTitle.next() + "\t" );
			}
			sb.append("\n");	
		}

		//PRINT : RESULT		
		int cnt = 1;
		Iterator<HashMap<String, String>> itResult = result.iterator();
		if(getResult!=null){
			while(itResult.hasNext()){

				HashMap<String, String> map =  itResult.next();
				sb.append( cnt + "\t" );
				Iterator<String> itField = getResult.iterator();
				
				while(itField.hasNext()){
					sb.append( map.get( itField.next() ) + "\t" );
				} 
				sb.append("\n");
				cnt++;
			}
			
		}
		COMUtil.debug( sb.toString() );
	}
}
